﻿
Partial Class Tools_ChangeDB
    Inherits MyUI.PageBaseApp

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Server.ScriptTimeout = 60 * 60 * 24 * 3

        Me.Response.BufferOutput = False

        Dim oo = DB.TAB.User_Order.GetByPK(1)

        'oo.Process()



    End Sub

    Protected Sub BtnCreateClassDB_Click(sender As Object, e As System.EventArgs) Handles BtnCreateClassDB.Click

        'Dim strConn = String.Format(Web.Configuration.WebConfigurationManager.ConnectionStrings("DB_MAIN").ToString)
        Dim DB As DBase.MySql.Tools.CreateDbClass

        DB = New DBase.MySql.Tools.CreateDbClass With {
            .ConnStr = String.Format(Web.Configuration.WebConfigurationManager.ConnectionStrings("App").ToString),
            .filepath = Server.MapPath("~\App_Code\DBase\Glouk.vb"),
            .DBName = "Glouk", .ClassNameDB = "GloukDB", .NameSpaceDB = "MyDBase"}
        DB.CreateClass()



    End Sub

    Protected Sub BtnReorderQueryPost_Click(sender As Object, e As EventArgs) Handles BtnReorderQueryPost.Click

        AddTAb(
            da:=1, a:=100, t:=mDB.Archive_Data.Type.QUERY_POST,
            TabDest:="glouk_post.query_post_{0}",
            TabDestsqlcreate:=
            "CREATE TABLE IF NOT EXISTS {0} (" &
                "id int NOT NULL AUTO_INCREMENT," &
                "post_code varchar(40) NOT NULL DEFAULT ''," &
                "query_id int NOT NULL," &
                "post_text text NOT NULL," &
                "pubb_date timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'," &
                "image_url varchar(255) NOT NULL DEFAULT ''," &
                "share_count int NOT NULL DEFAULT '0'," &
                "account_code varchar(30) NOT NULL DEFAULT ''," &
                "account_follower_count int NOT NULL DEFAULT '0'," &
                "account_username varchar(100) NOT NULL DEFAULT ''," &
                "PRIMARY KEY (id)," &
                "KEY query_id (query_id,pubb_date) ," &
                "KEY post_code (post_code)" &
            ") ENGINE=MyISAM "
        )

        Spezzadati(t:=mDB.Archive_Data.Type.QUERY_POST,
            TabMaster:="query", TabMasterFieldPK:="id", TabMasterFieldArchiveid:="archive_post_id",
            TabSource:="query_post", TabSourceFieldLink:="query_id",
            TabDestsqlcopy:=
            "insert into {0} " &
                "(select NULL,post_code ,query_id ,post_text ,pubb_date ,image_url ,share_count ,account_code ,account_follower_count ,account_username  from query_post where query_id = {1} )"
        )

    End Sub

    Protected Sub BtnReorderQueryaccount_Click(sender As Object, e As EventArgs) Handles BtnReorderQueryaccount.Click

        AddTAb(
            da:=1, a:=100, t:=mDB.Archive_Data.Type.QUERY_ACCOUNT,
            TabDest:="glouk_post.query_account_{0}",
            TabDestsqlcreate:=
            "CREATE TABLE IF NOT EXISTS {0} (" &
                "query_id int NOT NULL," &
                "account_code varchar(30) NOT NULL DEFAULT ''," &
                "post_update timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'," &
                "account_username varchar(100) NOT NULL DEFAULT ''," &
                "PRIMARY KEY (query_id,account_code,post_update)" &
            ") ENGINE=MyISAM "
        )

        Spezzadati(t:=mDB.Archive_Data.Type.QUERY_ACCOUNT,
            TabMaster:="query", TabMasterFieldPK:="id", TabMasterFieldArchiveid:="archive_account_id",
            TabSource:="query_account", TabSourceFieldLink:="query_id",
            TabDestsqlcopy:=
            "insert into {0} " &
                "(select * from query_account where query_id = {1} )"
        )

    End Sub
    Protected Sub BtnReorderTrend_Click(sender As Object, e As EventArgs) Handles BtnReorderTrend.Click

        AddTAb(
            da:=1, a:=100, t:=mDB.Archive_Data.Type.ACCOUNT_TREND,
            TabDest:="glouk_post.trend_{0}",
            TabDestsqlcreate:=
            "CREATE TABLE IF NOT EXISTS {0} (" &
                "account_id int NOT NULL DEFAULT '0'," &
                "trend_type_id int NOT NULL DEFAULT '0'," &
                "trend_date timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'," &
                "value int NOT NULL DEFAULT '0'," &
                "PRIMARY KEY (account_id,trend_type_id,trend_date)" &
            ") ENGINE=MyISAM")

        Spezzadati(t:=mDB.Archive_Data.Type.ACCOUNT_TREND,
            TabMaster:="account", TabMasterFieldPK:="id", TabMasterFieldArchiveid:="archive_trend_id",
            TabSource:="trend", TabSourceFieldLink:="account_id",
            TabDestsqlcopy:="insert into {0} " &
                "(select * from trend where account_id = {1} )"
        )

    End Sub
    Protected Sub BtnReorderAccountLink_Click(sender As Object, e As EventArgs) Handles BtnReorderAccountLink.Click

        AddTAb(
            da:=1, a:=10, t:=mDB.Archive_Data.Type.ACCOUNT_LINK,
            TabDest:="glouk_account.account_link_{0}",
            TabDestsqlcreate:=
            "CREATE TABLE IF NOT EXISTS {0} (" &
                "account_id int(11) NOT NULL DEFAULT '0'," &
                "link_type_id int(11) NOT NULL DEFAULT '0'," &
                "account_link_id int(11) NOT NULL DEFAULT '0'," &
                "updating bit(1) NOT NULL DEFAULT b'0'," &
                "create_date timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'," &
                "analysis bit(1) NOT NULL DEFAULT b'0'," &
                "excluded bit(1) NOT NULL DEFAULT b'0'," &
                "PRIMARY KEY (account_id,link_type_id,account_link_id)," &
                "KEY account_id (account_id,analysis)" &
            ") ENGINE=MyISAM")


        Spezzadati(t:=mDB.Archive_Data.Type.ACCOUNT_LINK,
            TabMaster:="account_update", TabMasterFieldPK:="account_id", TabMasterFieldArchiveid:="archive_link_id",
            TabSource:="account_link", TabSourceFieldLink:="account_id",
            TabDestsqlcopy:="insert into {0} " &
                "(select * from account_link where account_id = {1} )"
        )


    End Sub


    Protected Sub BtnReorderPost_Click(sender As Object, e As System.EventArgs) Handles BtnReorderPost.Click

        'Exit Sub

        '****
        Dim arttype As mDB.Archive_Data.Type = GloukDB.Archive_Data.Type.ACCOUNT_POST
        Dim TabSource = "account_post"
        Dim TabDest = "glouk_post.account_post_"
        Dim TabMAster = "account"
        Dim FieldLink = "account_id"
        Dim FieldArchiveid = "Archive_Post_Id"

        '---------------------------------------------
        ' INSERISCI LE TAB DI RIFERIMENTO

        Me.Response.BufferOutput = False

        For ct = 1 To 100

            Dim Tabnmae = TabDest & Format(ct, "000")

            Dim arc = DB.TAB.Archive_Data.
                Where("table_name", "=", Tabnmae).
                Where("archive_type_id", "=", arttype).
            ToObj

            If arc Is Nothing Then
                arc = New mDB.Archive_Data With {.Table_Name = Tabnmae, .Archive_Type_Id = arttype}
                DB.InsertOnSubmit(arc)
                DB.SubmitChanges()
            End If

            '****
            DB.ExecuteCommand(
                "CREATE TABLE IF NOT EXISTS " & Tabnmae & " (" &
                    "id int NOT NULL AUTO_INCREMENT," &
                    "post_code varchar(80) NOT NULL DEFAULT ''," &
                    "account_id int NOT NULL DEFAULT '0'," &
                    "post_text text NOT NULL," &
                    "pubb_date timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'," &
                    "PRIMARY KEY (id)," &
                    "UNIQUE KEY post_code (post_code) ," &
                    "KEY account_id (account_id,pubb_date) " &
                ") ENGINE=MyISAM "
            )

        Next

        '---------------------------------------------
        ' CONTEGGIO DEGLI ELEMENTI GIà ESISTENTI

        Dim arcdata = DB.TAB.Archive_Data.Where("archive_type_id", "=", arttype).ToList

        For Each ad In arcdata


            Dim acc_count = DB.GetQuery.From(ad.Table_Name).Select("count(distinct(" & FieldLink & ")) as tot").ToObj(Of Integer)()

            ad.Record_Count = acc_count


            DB.SubmitChanges()


        Next

        '---------------------------------------------
        ' SPOSTA DI POST NELLE TAB DI ARCHIVIO

        Do

            UI.Page.WriteLog(SF("account query"))

            Dim Accounts = DB.GetQuery(Of mDB.Account).NotifyChanged(True).Sql(
                "select * from " &
                "(select id from " & TabMAster & " where " & FieldArchiveid & " = 0 ) as TabSource " &
                "where (select count(*) from " & TabSource & " where " & TabSource & "." & FieldLink & " = TabSource.id limit 2) > 0 limit 1000 "
            ).ToList

            If Accounts.Count = 0 Then
                Exit Do
            End If

            Dim ct = 0
            For Each Acc In Accounts
                ct += 1

                Dim MinArc = (From arc In arcdata Order By arc.Record_Count).First

                '****
                DB.ExecuteCommand(
                    "insert into " & MinArc.Table_Name & "  " &
                    "(select NULL,post_code,account_id,post_text,pubb_date from account_post where account_id = " & Acc.Id & " group by post_code order by pubb_date desc  limit 200) "
                )

                'Acc.Archive_Post_Id = MinArc.Id
                DB.SetFieldValue(Acc, FieldArchiveid, MinArc.Id)
                MinArc.Record_Count += 1

                DB.SubmitChanges()

                UI.Page.WriteLog(SF("{2} - " & TabMAster & ": {0}, archive: {1}", Acc.Fullname, MinArc.Table_Name, ct))

            Next


        Loop Until False

    End Sub

    Sub AddTAb(da As Integer, a As Integer, t As mDB.Archive_Data.Type, TabDest As String, TabDestsqlcreate As String)

        For ct = da To a

            Dim Tabnmae = SF(TabDest, Format(ct, "000"))

            Dim arc = DB.TAB.Archive_Data.
                Where("table_name", "=", Tabnmae).
                Where("archive_type_id", "=", t).
            ToObj

            If arc Is Nothing Then
                arc = New mDB.Archive_Data With {.Table_Name = Tabnmae, .Archive_Type_Id = t}
                DB.InsertOnSubmit(arc)
                DB.SubmitChanges()
            End If

            '****
            DB.ExecuteCommand(SF(TabDestsqlcreate, Tabnmae))

        Next

    End Sub


    Protected Sub Spezzadati(t As mDB.Archive_Data.Type, TabSource As String, TabMaster As String, TabSourceFieldLink As String, TabMasterFieldArchiveid As String, TabMasterFieldPK As String, TabDestsqlcopy As String)

        '---------------------------------------------
        ' INSERISCI LE TAB DI RIFERIMENTO

        Me.Response.BufferOutput = False

        'For ct = 1 To 100

        '    Dim Tabnmae = SF(TabDest, Format(ct, "000"))

        '    Dim arc = DB.TAB.Archive_Data.
        '        Where("table_name", "=", Tabnmae).
        '        Where("archive_type_id", "=", t).
        '    ToObj

        '    If arc Is Nothing Then
        '        arc = New mDB.Archive_Data With {.Table_Name = Tabnmae, .Archive_Type_Id = t}
        '        DB.InsertOnSubmit(arc)
        '        DB.SubmitChanges()
        '    End If

        '    '****
        '    DB.ExecuteCommand(SF(TabDestsqlcreate, Tabnmae))

        'Next

        '---------------------------------------------
        ' CONTEGGIO DEGLI ELEMENTI GIà ESISTENTI

        'Dim arcdata = DB.TAB.Archive_Data.Where("archive_type_id", "=", t).ToList

        'For Each ad In arcdata


        '    Dim acc_count = DB.GetQuery.From(ad.Table_Name).Select("count(distinct(" & TabSourceFieldLink & ")) as tot").ToObj(Of Integer)()

        '    ad.Record_Count = acc_count


        '    DB.SubmitChanges()


        'Next

        '---------------------------------------------
        ' SPOSTA DI POST NELLE TAB DI ARCHIVIO
        Dim arcdata = DB.TAB.Archive_Data.Where("archive_type_id", "=", t).ToList

        Do

            UI.Page.WriteLog(SF("account query"))

            Dim Accounts = DB.GetQuery.NotifyChanged(True).Sql(
                "select " & TabMasterFieldPK & " from " &
                "(select " & TabMasterFieldPK & " from " & TabMaster & " where " & TabMasterFieldArchiveid & " = 0 ) as TabSource " &
                "where (select count(*) from " & TabSource & " where " & TabSource & "." & TabSourceFieldLink & " = TabSource." & TabMasterFieldPK & " limit 2) > 0 limit 1000 "
            ).ToList(Of Integer)()

            If Accounts.Count = 0 Then
                Exit Do
            End If

            Dim ct = 0
            For Each Acc In Accounts
                ct += 1

                Dim MinArc = (From arc In arcdata Order By arc.Record_Count).First

                '****
                DB.ExecuteCommand(SF(TabDestsqlcopy, MinArc.Table_Name, Acc))

                'Acc.Archive_Post_Id = MinArc.Id
                DB.GetQuery.Update(TabMaster).UpdateValue(TabMasterFieldArchiveid, MinArc.Id).Where(TabMasterFieldPK, "=", Acc).ExecuteCommand()

                ' DB.SetFieldValue(Acc, TabMasterFieldArchiveid, MinArc.Id)
                MinArc.Record_Count += 1

                DB.SubmitChanges()

                UI.Page.WriteLog(SF("{2} - " & TabMaster & ": {0}, archive: {1}", Acc, MinArc.Table_Name, ct))

            Next


        Loop Until False

    End Sub



    Protected Sub BtnUpdateHash_Click(sender As Object, e As EventArgs) Handles BtnUpdateHash.Click

        Dim share = DB.TAB.Share_Post.ToList

        For Each s In share
            s.Hash_Id = s.Post_Text.GetHashCode
            DB.SubmitChanges()
        Next

        Dim mess = DB.TAB.Message_Post.ToList

        For Each s In mess
            s.Hash_Id = s.Post_Text.GetHashCode
            DB.SubmitChanges()
        Next



    End Sub

    Protected Sub BtnUpdatenetwork_Click(sender As Object, e As EventArgs) Handles BtnUpdatenetwork.Click

        Dim q = DB.GetQuery

        DB.TAB.Account.Update.UpdateValue("network_id", 100).ExecuteCommand()
        DB.TAB.User_Account.Update.UpdateValue("network_id", 100).ExecuteCommand()
        DB.TAB.Query.Update.UpdateValue("network_id", 100).ExecuteCommand()

    End Sub

    'Protected Sub BtnGenderTab_Click(sender As Object, e As EventArgs) Handles BtnGenderTab.Click

    '    Dim q = DB.GetQuery

    '    q.Sql("truncate table gender_name").ExecuteCommand()

    '    q.Sql("insert into gender_name (name,gender_type_id1) select name, if(gender='male',1,if(gender='female',2,3)) as id from gender_db_1 ").ExecuteCommand()


    '    Dim g2 = DB.TAB.Gender_Db_2.NotifyChanged(False).ToList

    '    For Each g In g2

    '        Dim genname = DB.TAB.Gender_Name.BY.Gender_Name.Name(g.Name).ToObj
    '        If genname Is Nothing Then
    '            genname = New mDB.Gender_Name With {.Name = g.Name}
    '            DB.TAB.Gender_Name.InsertOnSubmit(genname)
    '        End If

    '        genname.Gender_Type_Id2 = g.Gender_Type_Id
    '        DB.SubmitChanges()

    '    Next

    '    q.Sql("update gender_name set gender_type_id1 = -1 where gender_type_id1 = 0").ExecuteCommand()
    '    q.Sql("update gender_name set gender_type_id2 = -1 where gender_type_id2 = 0").ExecuteCommand()


    'End Sub

    Protected Sub BtnGenderTab_Click(sender As Object, e As EventArgs) Handles BtnGenderTab.Click

        Dim q = DB.GetQuery

        q.Sql("truncate table gender_name").ExecuteCommand()

        q.Sql("insert into gender_name (name,gender_type_id) select name, if(gender='male',1,if(gender='female',2,3)) as id from gender_db_1 ").ExecuteCommand()
        q.Sql("insert into gender_name (name,gender_type_id,lang,freq) select  name, gender_type_id,lang,freq from gender_db_2").ExecuteCommand()



        Dim Gender = DB.TAB.Gender_Name.ToList

        For Each g In Gender

            g.Name = Regex.Replace(g.Name, "\(.{0,3}\)", "").Trim

        Next

        DB.SubmitChanges()


    End Sub


    Protected Sub BtnSourceFollAcc_Click(sender As Object, e As EventArgs) Handles BtnSourceFollAcc.Click

        Dim q = DB.GetQuery

        q.Sql("update auto_follow_account set source_description =COALESCE((select concat(query,' (',lang_code,')') from query where id = auto_follow_account.source_id),'') where source_type_id = 1").ExecuteCommand()

        q.Sql("update auto_follow_account set source_description =COALESCE(concat( '@' , (select username from account where id = auto_follow_account.source_id)),'') where source_type_id = 2").ExecuteCommand()



    End Sub


    Protected Sub BtnAlterQueryQccount_Click(sender As Object, e As EventArgs) Handles BtnAlterQueryQccount.Click

        Dim arc = DB.TAB.Archive_Data.BY.Archive_Data.Archive_Type_Id(mDB.Archive_Data.Type.QUERY_ACCOUNT).ToList
        For Each a In arc
            Try
                DB.ExecuteCommand(
                    "update  " & a.Table_Name & " set post_count=1, account_follower_count=1"
                )
                'DB.ExecuteCommand(
                '    "ALTER TABLE " & a.Table_Name & " " &
                '    "ADD COLUMN post_count  int NOT NULL DEFAULT 0 AFTER post_update, " &
                '    "ADD COLUMN account_follower_count  int NOT NULL DEFAULT 0 AFTER account_username"
                ')


            Catch ex As Exception
                UI.Page.WriteLog(ex.Message & " - " & ex.InnerException.Message)
            End Try

        Next



    End Sub

    Protected Sub BtnUpdateDic2014_Click(sender As Object, e As EventArgs) Handles BtnUpdateDic2014.Click

        DB.ExecuteCommand("update account_update INNER join account_add_info USING(account_id) set  account_add_info.profile_update = account_update.profile_update ")
        DB.ExecuteCommand("update account_update INNER join account_add_info USING(account_id) set  account_add_info.post_count = account_update.post_count ")
        DB.ExecuteCommand("update account_update INNER join account_add_info USING(account_id) set  account_add_info.post_update = account_update.post_update ")
        DB.ExecuteCommand("update account_update INNER join account_add_info USING(account_id) set  account_add_info.post_update_count = account_update.post_update_count ")
        DB.ExecuteCommand("update account_update INNER join account_add_info USING(account_id) set  account_add_info.post_last_code = account_update.post_last_code ")
        DB.ExecuteCommand("update account_update INNER join account_add_info USING(account_id) set  account_add_info.follower_count = account_update.follower_count ")
        DB.ExecuteCommand("update account_update INNER join account_add_info USING(account_id) set  account_add_info.friend_count = account_update.friend_count ")

        DB.ExecuteCommand("update account INNER join account_update on account.id = account_update.account_id set  account_update.archive_trend_id = account.archive_trend_id ")

        DB.ExecuteCommand("delete from account_update where account_id not in (select account_id from account_auth ) ")



    End Sub

    Protected Sub BtnTimeZone_Click(sender As Object, e As EventArgs) Handles BtnTimeZone.Click

        Dim q = DB.GetQuery

        q.Sql("truncate table time_zone").ExecuteCommand()

        Dim timeZones = TimeZoneInfo.GetSystemTimeZones()

        For Each timeZone As TimeZoneInfo In timeZones

            Dim t = New mDB.Time_Zone With {
                .Time_Zone_Code = timeZone.Id,
                .Daylight_Saving = timeZone.SupportsDaylightSavingTime,
                .Display_Name = timeZone.DisplayName, .Utc_Offset = timeZone.BaseUtcOffset.TotalSeconds
                }

            DB.InsertOnSubmit(t)
            DB.SubmitChanges()


            'Dim hasDST As Boolean = timeZone.SupportsDaylightSavingTime
            'Dim offsetFromUtc As TimeSpan = timeZone.BaseUtcOffset
            'Dim adjustRules() As System.TimeZoneInfo.AdjustmentRule
            'Dim offsetString As String

            'UI.Page.WriteLog("ID: {0}", timeZone.Id)
            'sw.WriteLine("   Display Name: {0, 40}", timeZone.DisplayName)
            'sw.WriteLine("   Standard Name: {0, 39}", timeZone.StandardName)
            'sw.Write("   Daylight Name: {0, 39}", timeZone.DaylightName)
            'sw.Write(IIf(hasDST, "   ***Has ", "   ***Does Not Have "))
            'sw.WriteLine("Daylight Saving Time***")
            'offsetString = String.Format("{0} hours, {1} minutes", offsetFromUtc.Hours, offsetFromUtc.Minutes)
            'sw.WriteLine("   Offset from UTC: {0, 40}", offsetString)
            'adjustRules = timeZone.GetAdjustmentRules()
            'sw.WriteLine("   Number of adjustment rules: {0, 26}", adjustRules.Length)

        Next

    End Sub
End Class
